﻿using datatablegenerator.Common;
using datatablegenerator.DB;
using datatablegenerator.Entity;
using datatablegenerator.Enums;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Windows;

namespace datatablegenerator.Factory.DBFactory.Provider
{
    public class MySqlProvider : IDbContext
    {
        public DbConnection conn { get; set; }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        /// <returns></returns>
        public Tuple<bool, string> CloseConnection()
        {
            try
            {
                if (conn.State == ConnectionState.Closed)
                {
                    return Tuple.Create(true, "连接已关闭！");
                }

                conn.Close();
                if (conn.State != System.Data.ConnectionState.Closed)
                {
                    return Tuple.Create(true, "连接关闭失败！");
                }
            }
            catch (Exception ex)
            {
                return Tuple.Create(false, ex.Message);
            }
            return Tuple.Create(true, "连接已关闭！");
        }

        /// <summary>
        /// 数据库连接
        /// </summary>
        /// <param name="connectionStr">连接字符串</param>
        /// <returns></returns>
        public Tuple<bool, string> ConnectionDb(string connectionStr)
        {
            conn = new MySqlConnection(connectionStr);
            try
            {
                if (conn.State == ConnectionState.Open)
                {
                    ConfigurationManager.AppSettings.Set("MySQL", $"{connectionStr}");
                    return Tuple.Create(true, "连接成功！");
                }
                conn.Open();
                if (conn.State != System.Data.ConnectionState.Open)
                {
                    return Tuple.Create(true, "连接失败！");
                }

            }
            catch (MySqlException ex)
            {
                return Tuple.Create(false, ex.Message);
            }
            return Tuple.Create(true, "连接成功！");
        }

        /// <summary>
        /// 创建数据表
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="dt"></param>
        /// <param name="dto"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public Tuple<bool, string> CreateDataTable(string sql, string dt, List<DatatableDTO> dto)
        {
            try
            {
                var result = MySqlDBHelper.Execute(sql);

                if (result < 0)
                {
                    return Tuple.Create(false, $"{dt}表,生成失败！");
                }

                return Tuple.Create(true, $"{dt}表,生成成功！");
            }
            catch (Exception ex)
            {
                return Tuple.Create(false, $"{ex.Message}");
            }
        }

        /// <summary>
        /// 创建数据表SQL
        /// </summary>
        /// <param name="dt">数据表</param>
        /// <param name="dto">解析xlsx数据</param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public Tuple<bool, string> CreateDataTableSQL(string dt, List<DatatableDTO> dto)
        {
            string content = string.Empty;
            string primarykey = string.Empty;
            foreach (var item in dto)
            {
                try
                {
                    //TODO: 待优化
                    string name = item.Name;
                    string type = item.Type;
                    string required = item.Required == true ? "NOT NULL" : "DEFAULT NULL";

                    var result = DataTypeHelper.ConstraintStatus(item.Constraint, dto);
                    if (!result.Success)
                    {
                        return Tuple.Create(false, result.Content);
                    }
                    // 记录主键字段
                    if (result.Success && !string.IsNullOrWhiteSpace(result.Content))
                    {
                        primarykey = item.Name;
                    }

                    string defaults = string.IsNullOrWhiteSpace(item.Default) ? "" : DataTypeHelper.IsCharType(item.Type.Trim()) == true ? $" default('{item.Default}')" : $" default({item.Default})";

                    content += string.Format($"`{name}` {type} {required} Comment '{item.Remacks}',");

                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "提示", MessageBoxButton.OK, MessageBoxImage.Error);
                }
            }
            var info = AnalyzeConnectionString(DBType.MySql);

            return Tuple.Create(true, string.Format($"CREATE TABLE `{dt}` ( {content} PRIMARY KEY (`{primarykey}`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;"));
        }

        /// <summary>
        /// 判断数据表是否存在
        /// </summary>
        /// <param name="table">表名</param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public Tuple<bool, string> IsTableExist(string table)
        {
            var dataBase = AnalyzeConnectionString(DBType.MySql);
            var result = MySqlDBHelper.Query($"SHOW TABLES IN {dataBase.Item2} LIKE '{table}'");

            if (result.Rows.Count > 0)
            {
                return Tuple.Create(false, $"该数据表【{table}】,已存在！");
            }

            return Tuple.Create(true, "不存在");
        }

        /// <summary>
        /// 分析连接字符串
        /// </summary>
        /// <param name="dBType">数据库类型</param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public Tuple<bool, string> AnalyzeConnectionString(DBType dBType)
        {
            return Tuple.Create(true, "ys_backstage_i5");
        }
    }
}
